const CryptoJS = require('crypto-js')
const {QueryTypes, db, Op} = require("../models/db");
const pageParams = require("./cols");
const _ = require("lodash");
const xe = require("xe-utils");
const v4 =  require("uuid");
const fs = require("fs");
const xlsx = require("node-xlsx");
const qiniu = require("qiniu");
const cnchar = require("cnchar");
const fileSaveDir = 'D:/sanzhi/down/web' // 导出文件保存的目录
let downUrl = 'https://download.sanzhiyun.cn' // 下载地址
const dayjs = require('dayjs')
const weekday = require('dayjs/plugin/weekday')
require('dayjs/locale/zh-cn')
const {setTreeTableCode} = require("./treeTableSave");
const serieal = require("serialize-javascript");
dayjs.extend(weekday)
dayjs.locale('zh-cn')
let tool = {
    downUrl: downUrl,
    xe: xe,
    _: _,
    dayjs: dayjs,
    add: (...vars) => {
        let len = vars.length
        let sum = 0
        for (let i = 0; i < len; i++) {
            sum = xe.add(sum,vars[i])
        }
        return sum
    },
    minus: (...vars) => {
        let len = vars.length
        let sum = vars[0]
        for (let i = 1; i < len; i++) {
            sum = xe.subtract(sum,vars[i])
        }
        return sum
    },
    multiply: (...vars) => {
        let len = vars.length
        let sum = vars[0]
        for (let i = 1; i < len; i++) {
            sum = xe.multiply(sum,vars[i])
        }
        return sum
    },
    divide: (...vars) => {
        let len = vars.length
        let sum = vars[0]
        for (let i = 1; i < len; i++) {
            sum = xe.divide(sum,vars[i])
        }
        return sum
    },
    obj2str: (obj) => {
        return serieal(obj)
    },
    str2obj: (str) => {
        return new Function('return ' + str)()
    },
    save: async (ctx) => {
        let formData = ctx.request.body.formData;
        let mod = ctx.request.body.mod;
        let isTree = ctx.request.body?.isTree
        if(!mod) return ctx.err('未知模型名称')
        let attrs = db.models[mod].getAttributes()
        for(let i in attrs) {
            if(i === 'ctime') {
                if(!formData.id) formData.ctime = tool.getTime()
            }
            if(i === 'ct') {
                if(!formData.id) formData.ct = tool.getTime()
            }
            if(i === 'utime') {
                formData.utime = tool.getTime()
            }
            if(i === 'ut') {
                formData.ut = tool.getTime()
            }
            if(i === 'tenantId') {
                if(!formData.id) formData.tenantId = ctx.user.tenantId
            }
            if(i === 'pinYin') {
                formData.pinYin = formData.name ? tool.py(formData.name) : null
            }
            if(i === 'code') {
                if(isTree && !formData.id) {
                    // 新增设置树形表的code
                    await setTreeTableCode(mod, formData, ctx)
                }
            }
            if(i === 'cby') {
                if(!formData.id) {
                    formData.cby = ctx.user.id
                }
            }
            if(i === 'uby') {
                formData.uby = ctx.user.id
            }
        }
        for(let i in formData) {
            if(formData[i] === '') {
                formData[i] = null
            }
        }
        if (formData.id) {
            // 修改保存
            await db.models[mod].update(formData, {
                where: {id: formData.id}
            })
            ctx.suc('保存成功', formData.id)
        }
        else {
            // 新增
            let ret = await db.models[mod].create(formData)
            ctx.suc('保存成功', ret)
        }
    },
    leftShop: async (id, autoLeft = 0) => {
        let model = await db.models['contactBf'].findOne({
            where: {
                id: id
            }
        })
        if(!model) return {
            stat: false,
            data: '未找到到店记录'
        }
        let leftTime = tool.getTime()
        if(tool.dayjs(leftTime).isBefore(model.arrived)) {
            return {stat: false, data: '离店时间返回错误，请再试一次'}
        }
        let dur = tool.dayjs(leftTime).unix() - tool.dayjs(model.arrived).unix()
        model.leftTime = leftTime
        model.state = '已完成'
        model.duration = dur
        model.durationStr = tool.secondsToStr(dur)
        model.autoLeft = autoLeft
        await model.save()
        return {stat: true, data: model.leftTime}
    },
    login: async (username, userpwd, ctx) => {
        let allowErrCount = 6; // 允许错误次数
        let user = await db.models['admin'].findOne({
            where: {
                username: username
            }
        })
        if(!user) {
            user = await db.models['admin'].findOne({
                where: {
                    mobile: username
                }
            })
        }
        if (!user) return ctx.err('未找到该用户');
        // return ctx.err('err')

        let errCount = user.errCount; // 已存在的错误次数
        errCount = errCount ? errCount : 0;
        if (errCount > (allowErrCount - 1)) {
            return ctx.err('错误次数超过 ' + allowErrCount + ' 次,账户已被锁定');
        }
        if (userpwd.length > 20) {
            if (user.userpwd !== userpwd) {
                // 增加错误次数
                user.errCount = errCount + 1
                await user.save()
                return ctx.err('用户名或密码错误');
            }
        }
        else {
            if (user.userpwd !== tool.crypto.MD5(userpwd)) {
                // 增加错误次数
                user.errCount = errCount + 1
                await user.save()
                return ctx.err('用户名或密码错误');
            }
        }
        // 错误次数大于0时，当正确后清空错误次数
        if (errCount > 0) {
            user.errCount = 0
            await user.save()
        }
        if (user.disabled === 2) {
            return ctx.err('用户已被删除')
        }
        if (user.disabled === 1) {
            return ctx.err('账号已被禁用')
        }
        let tenantId = user.tenantId;
        let tenant = await db.models['tenant'].findOne({
            where: {id: tenantId}
        })
        user.tenantName = tenant.name;
        let ob = await db.query(`select * from options where tenantId=${tenantId} and name='system' `, {type: QueryTypes.SELECT, plain: true})
        user.options = ''
        if (ob) {
            user.options = ob.value
        }
        if(user.roleId) {
            let role = await db.models['role'].findOne({
                where: {id: user.roleId}
            })
            if(role) user.roleName = role.name
        }
        let ret = user.toJSON()
        ret.isCard = tenant.isCard
        return ctx.suc('登录成功', ret);
    },
    getDay: (day= '', diff = 0, addType = 'DAY', format = 'YYYY-MM-DD') => {
        return dayjs(day?day:dayjs()).add(diff, addType).format(format)
    },
    dayDif: (day1, day2, unit = 'day') => {
        return dayjs(day1).diff(dayjs(day2), unit)
    },
    getTime: (day= '', diff = 0, addType = 'day', format = 'YYYY-MM-DD HH:mm:ss') => {
        return  dayjs(day ? day : dayjs()).add(diff, addType).format(format)
    },
    getDayStart: (current = '', type = 'beg', diff = 0, unit = 'MONTH') => {
        if (type === 'beg'){
            return   dayjs(current ? current : dayjs()).add(diff, unit).startOf(unit).format('YYYY-MM-DD')
        }
        if (type === 'end'){
            return   dayjs(current ? current : dayjs()).add(diff, unit).endOf(unit).format('YYYY-MM-DD')
        }
    },
    crypto: {
        //MD5加密
        MD5(data) {
            return CryptoJS.MD5(data).toString()
        },
    },
    page: async (ctx, sql) => {
        let whereArr = []
        let pageSize = ctx.request.body.pageSize ? ctx.request.body.pageSize : 200;
        let currentPage = ctx.request.body.currentPage?ctx.request.body.currentPage:1;
        let offset = (currentPage - 1) * pageSize;
        let order = ctx.request.body.order; // 排序
        let sort = ctx.request.body.sort; // 组合排序
        let excel = ctx.request.body.excel; // 是否导出
        let searchTxt = ctx.request.body.searchTxt; // where数组
        let options = {type: QueryTypes.SELECT};
        let params = pageParams(ctx, tool) // cols文件中的列配置
        let where = tool.combSearch(ctx); // where数组
        if(!sql) {
            if(params.sql) sql = params.sql
        }
        if(xe.isArray(where) && where.length > 0) {
            sql += ' and ' + where.join(' and ')
        }
        else {
            if(!searchTxt) {

            }
            else {
                let quick = params ? (params.quick ? params.quick : null) : null
                if(quick) sql += ` and ${quick} like '%${searchTxt}%' `
            }
            if(whereArr.length > 0) {
                let wh = ' and ' + whereArr.join(' and ')
                sql += wh
            }
        }
        let total = 0
        if(!excel) {
            let countModel = await db.query( `select count(*) count from (${sql}) a`, options );
            total = parseInt(countModel[0].count);
        }
        if(sort && typeof sort === 'object' && sort.length > 0) {
            let ors = []
            sort.map(row => {
                if(row.field === 'durationStr') {
                    ors.push(` duration ${row.order} `)
                }
               else {
                    ors.push(` ${row.field} ${row.order} `)
                }
            })
            sql = ` ${sql} order by ${ors.join(',') } `
        }
        else {
            sql = ` ${sql} ${order} `
        }
        if(excel) {
            let ret = await db.query(sql, options);
            if(ret.length === 0) return ctx.err('没有要导出的数据')
            let list = [
                {
                    name: params.fileName,
                    data: []
                }
            ]
            let t = []
            let f = []
            params.columns.map(col => {
                if(col.excel) {
                    t.push(col.title)
                    f.push(col.field)
                }
            })
            list[0].data.push(t)
            ret.map(row => {
                let one = []
                _.forEach(f, function (value, key) {
                    one.push(row[value])
                })
                list[0].data.push(one)
            })
            return  await tool.exportOut(params.fileName, list, ctx)
        }
        else {
            let ret = await db.query(sql + " offset " + offset + " rows fetch next " + pageSize + ' rows only; ', options);
            return ctx.suc('', {
                total,
                list: ret,
            })
        }
    },
    // 秒转时间
    secondsToStr: (seconds) => {
        let dayS = 24 * 60 * 60
        let day = parseInt(seconds/dayS)
        let l = seconds%dayS
        let hour = parseInt(l/(60*60))
        let l1 = l%(60*60)
        let minute = parseInt(l1/60)
        let sec = l1%60
        let str = '' //`${day}天${hour}时${minute}分${sec}秒`
        if(day > 0) str += day+'天'
        if(hour > 0) str += hour+'时'
        if(minute > 0) str += minute+'分'
        if(sec > 0) str += sec+'秒'
        return str
    },
    // 7牛云文件下载地址
    getPicUrl: (fileName, waterText = null, zoom = false) => {
        let returnWidth = 1200
        let returnHeight = 1200
        let returnQuantity = 90
        let roomW = 200
        const deadline = dayjs().unix() + 600; // 过期时间  10分钟
        let privateBucketDomain = 'https://qiniu.sanzhiyun.cn'
        const accessKey = 'Qv_OXaTQUf1Gi2dvH1Qe77wtLOdMuNMSPCUXzK6j';
        const secretKey = 'pEVQBydHGAZFEmG98ai6UJLL_TsdP-4dd0CxSXNF';
        const mac = new qiniu.auth.digest.Mac(accessKey, secretKey);
        const config = new qiniu.conf.Config();
        const bucketManager = new qiniu.rs.BucketManager(mac, config);
        let zooms = `imageView2/2/w/${roomW}/h/${roomW}/q/${returnQuantity}`
        let zoomsDefault = `imageView2/2/w/${returnWidth}/h/${returnHeight}/q/${returnQuantity}`
        let obj = {
            url: '',
            urlZoom: ''
        }
        let or = `${fileName}`
        let zo = `${fileName}`
        if(waterText) {
            or = or + `?watermark/4/text/${qiniu.util.urlsafeBase64Encode(waterText)}/fontsize/800/fill/${qiniu.util.urlsafeBase64Encode('white')}/dissolve/50/rotate/-40/uw/300/uh/300/resize/1|${zoomsDefault}`
            zo = zo + `?watermark/4/text/${qiniu.util.urlsafeBase64Encode(waterText)}/fontsize/800/fill/${qiniu.util.urlsafeBase64Encode('white')}/dissolve/50/rotate/-40/uw/300/uh/300/resize/1`
        }
        if (zoom) {
            if(waterText) {
                zo = zo + `|${zooms}`
            }
            else {
                zo = zo + `?${zooms}`
            }
        } else {
            // 默认缩放大小
            if(waterText) {
                zo = zo + `|${zoomsDefault}`
            }
            else {
                zo = zo + `?${zoomsDefault}`
            }
        }
        obj.url = bucketManager.privateDownloadUrl(privateBucketDomain, or, deadline);
        obj.urlZoom = bucketManager.privateDownloadUrl(privateBucketDomain, zo, deadline);
        return obj
    },
    uuid: () => {
        return v4.v4();
    },
    py: (str) => {
        return cnchar.spell(str, 'first', 'low')
    },
    getDistance (lng1, lat1, lng2, lat2) {
        let radLat1 = lat1*Math.PI / 180.0;
        let radLat2 = lat2*Math.PI / 180.0;
        let a = radLat1 - radLat2;
        let b = lng1*Math.PI / 180.0 - lng2*Math.PI / 180.0;
        let s = 2 * Math.asin(Math.sqrt(Math.pow(Math.sin(a/2),2) +
            Math.cos(radLat1)*Math.cos(radLat2)*Math.pow(Math.sin(b/2),2)));
        s = s *6378.137 *  1000 ;// EARTH_RADIUS;
        return tool.xe.round(s, 4);
    },
    // 权限SQL
    authSql: (ctx) => {
        if (ctx.user) {
            let allData = ctx.user.allData
            let selfData = ctx.user.selfData
            let roleName = ctx.user.roleName
            let uid = ctx.user.id
            let tid = ctx.user.tenantId
            if (allData) {
                return ''
            }
            else if (roleName === '管理员') {
                return ''
            }
            else if (selfData) {
                return ` select ${uid} uid `
            }
            else {
                let auth = `SELECT b.id uid from admin_data_dept a inner join admin b on a.tenantId=${tid} and a.deptId=b.deptId and selfId=${uid} and b.tenantId=${tid} 
                union all SELECT owerId uid from admin_data_ower where tenantId=${tid} and selfId=${uid} union all  select ${uid} uid `
                return auth
            }
        }
        else {
             throw new Error('权限认证失败')
        }
    },
    exportOut: async (params, ret, ctx) => {
        if(ret.length === 0) return ctx.err('没有数据')
        let list = [
            {
                name: params.fileName,
                data: []
            }
        ]
        let t = []
        let f = []
        let columns = params.columns
        columns.map(col => {
            if(col.excel) {
                t.push(col.title)
                f.push(col.field)
            }
            if(col.children) {
                col.children.map(ch => {
                    if(ch.excel) {
                        t.push(ch.title)
                        f.push(ch.field)
                    }
                })
            }
        })
        console.log(t)
        list[0].data.push(t)
        ret.map(row => {
            let one = []
            _.forEach(f, function (value, key) {
                one.push(row[value])
            })
            list[0].data.push(one)
        })
        const buffer = xlsx.build(list)
        let filename = params.fileName + ctx.user.id.toString() + _.now() + '.xlsx'
        await fs.writeFileSync(fileSaveDir + '/' + filename, buffer)
        return  ctx.suc('', downUrl + '/web/'  + filename)
    },
    combSearch: (ctx) => {
        if(ctx.request.body.where && xe.isArray(ctx.request.body.where)) {
            return ctx.request.body.where
        }
        else {
            if( ctx.request.body.where && ctx.request.body.where.searchRule) {
                let formData = ctx.request.body.where.formData
                let searchRule = ctx.request.body.where.searchRule
                let where = []
                for (let i in formData) {
                    if (i && typeof formData[i] !== 'undefined' && formData[i] !== null) {
                        searchRule.map(s => {
                            if (s.field === i) {
                                let sop = s.sop ? s.sop : '='
                                if (sop === 'include') {
                                    where.push(` ${i} like '%${formData[i]}%' `)
                                }
                                else {
                                    if(s.dataType) {
                                        if(s.dataType === 'string') {
                                            if(s.realField) {
                                                where.push(` ${s.realField} ${s.sop} '${formData[i]}' `)
                                            }
                                            else {
                                                where.push(` ${i} ${s.sop} '${formData[i]}' `)
                                            }
                                        }
                                        else {
                                            if(s.realField) {
                                                where.push(` ${s.realField} ${s.sop} ${formData[i]} `)
                                            }
                                            else {
                                                where.push(` ${i} ${s.sop} ${formData[i]} `)
                                            }
                                        }
                                    }
                                    else {
                                        if(s.realField) {
                                            where.push(` ${s.realField} ${s.sop} ${formData[i]} `)
                                        }
                                        else {
                                            where.push(` ${i} ${s.sop} ${formData[i]} `)
                                        }
                                    }
                                }
                            }
                        })
                    }
                }
                return where
            }
            else {
                return ''
            }
        }
    },
    getMenu: async (ctx) =>{
        let menu = [];
        let user = ctx.user;
        let uid = user.id;
        if(user.username === 'admin') {
            menu = await db.models['menu'].findAll({
                order: ['sort']
            })
        } else {
            // let isMan = await db.query(`select * from users a inner join role b on a.roleid=b.id and a.id='${uid}' and b.name='管理员'`, {type: QueryTypes.SELECT, plain: true});
            // if(isMan) {
            //     menu = db.models['menu'].findAll({
            //         where: {
            //             isAdmin: 0
            //         },
            //         order: ['sort']
            //     })
            // } else {
            //     let menusModel = await db.query(`select b.menus from users a inner join role b on a.roleid=b.id and a.id='${uid}'`, {type: QueryTypes.SELECT, plain: true})
            //     let mid = []
            //     if (menusModel) {
            //         let ms =  menusModel.menus
            //         for (let ar in ms) {
            //             mid.push(ar)
            //         }
            //     }
            //     if(mid.length > 0) {
            //         menu = db.models['menu'].findAll({
            //             where: {
            //                 id: {
            //                     [Op.in]: mid
            //                 }
            //             }
            //         })
            //     }
            // }
        }
        return menu
    },
    // 取关联公司
    relCompany: async (tenantId) => {
        let list1 = []
        let list = await db.models['tenantCompany'].findAll({
            where: {
                tenantId: tenantId
            },
            attributes: ['companyId']
        })
        list.map(row => {
            list1.push(row.companyId)
        })
        return  list1;
    },
    // 待配送列表
    unDeliver: async (cargoId) => {
        let sqls = `SELECT d.tenantId, c.iid, b.cargoId,d.billNo,d.billDate,co.name buName,co.address,co.linkMobile,co.linkName,gs.name invName,gs.spec,c.unit,c.calNum,
    c.qty,gs.unit1,gs.unit2,c.invId,d.transType  from cargo_invoice b
    INNER JOIN invoice_info c on b.iid=c.iid
    inner join invoice d on b.iid=d.id
    inner join goods as gs on c.invId=gs.id
    inner join contact as co on d.buId=co.id
    where b.cargoId in(${cargoId}) and c.trans<>'换入'`
        return await db.query(sqls, {type: QueryTypes.SELECT})
    },
    autoLeft: async () => {
        try {
            let list = await db.query(`select * from contact_bf where state='未离店'`, {type: QueryTypes.SELECT})
            await db.transaction(async () => {
                for (let i = 0; i < list.length; i++) {
                    let row = list[i]
                    let leftTime = row.arrived.substring(0, 10) + ` ${tool.getTime().substring(11)}`
                    let dur = tool.dayjs(leftTime).unix() - tool.dayjs(row.arrived).unix()
                    let model = await db.models['contactBf'].findOne({
                        where: {
                            id: row.id
                        }
                    })
                    model.leftTime = leftTime
                    model.state = '已完成'
                    model.duration = dur
                    model.durationStr = tool.secondsToStr(dur)
                    model.autoLeft = 2
                    await model.save()
                }
            })
        } catch (e) {

        }
    },
    // 取子分类的IDs
    getChildIds: async (id, tbName) => {
        let sql = `
            with cte_child(id,name,pid,level) 
            as
            ( 
              --起始条件 
              select id,name,pid,0 as level
              from ${tbName} 
              where id = ${id}
              union all
              --递归条件 
              select a.id,a.name,a.pid,b.level+1 
              from ${tbName} a 
              inner join 
              cte_child b 
              on ( a.pid=b.id)  
            ) 
            select * from cte_child;
        `
        let list = await db.query(sql, {type: QueryTypes.SELECT})
        let arr = []
        list.map(row => {
            arr.push(row.id)
        })
        return arr
    },
    // 表模型设置默认值
    setDefault: (data, mod) => {
        let attrs = db.models[mod].getAttributes()
        for(let i in attrs) {
            let dataType = attrs[i].type.getDataTypeId()
            if(dataType === 'INTEGER' || dataType === 'TINYINT' || dataType === 'SMALLINT' || dataType === 'DECIMAL') {
                if(!data[i]) data[i] = 0
                if(data[i] == undefined) data[i] = 0
                if(data[i] == 'undefined') data[i] = 0
                if(data[i] == 'null') data[i] = 0
                if(data[i] == null) data[i] = 0
            }
            if(dataType === 'STRING' || dataType === 'TEXT') {
                if(!data[i]) data[i] = ''
                if(data[i] == undefined) data[i] = ''
                if(data[i] == 'undefined') data[i] = ''
                if(data[i] == 'null') data[i] = ''
                if(data[i] == null) data[i] = ''
            }
        }
    },
    initGoodsInventory: async (ctx, t) => {
        let tenantId = ctx.user.tenantId
        let goodsql = `select * from goods where tenantId=${tenantId}`
        let goods = await db.query(goodsql, {type: QueryTypes.SELECT})
        let storage = await db.models['storage'].findAll({
            where: {
                tenantId: ctx.user.tenantId
            }
        })
        for (let i = 0; i < goods.length; i++) {
            let good = goods[i]
            for (let i = 0; i < storage.length; i++) {
                let lid = storage[i].id
                let c = await db.models['inventoryDetail'].findOne({
                    where: {
                        tenantId: good.tenantId,
                        locationId: lid,
                        invId: good.id,
                        transTypeName: '期初'
                    }
                })
                let hasMain = await db.models['inventory'].findOne({
                    where: {
                        tenantId: good.tenantId,
                        locationId: lid,
                        invId: good.id
                    }
                })
                if(!c) {
                    let detail = db.models['inventoryDetail'].build()
                    detail.tenantId = good.tenantId
                    detail.locationId = lid
                    detail.invId = good.id
                    detail.billNo = ''
                    detail.relId = 0
                    detail.transType = 0
                    detail.balanceCount = 0
                    detail.balancePrice = good.price1?(xe.divide(good.price1, good.spec)):0
                    detail.balanceMoney = 0
                    detail.transTypeName = '期初'
                    detail.billDate = '1970-01-01'
                    detail.unit = good.unit2 ? good.unit2:good.unit1
                    detail.unit1 = good.unit1
                    detail.unit2 = good.unit2
                    detail.spec = good.spec
                    detail.calNum = 1
                    detail.seq = 1
                    detail.ctime = tool.getTime()
                    await detail.save()
                }
                if(c) {
                    if(!c.balanceCount) {
                        c.balancePrice = good.price1?(xe.divide(good.price1, good.spec)):0
                        await c.save()
                    }
                }
                if(!hasMain) {
                    let detail = db.models['inventory'].build()
                    detail.tenantId = good.tenantId
                    detail.locationId = lid
                    detail.invId = good.id
                    await detail.save()
                }
            }
        }
    },
    currentZq: async (tenantId) => {
        let zq = await db.models['zq'].findOne({
            where: {
                tenantId,
                isCurrent: 1
            }
        })
        if (!zq) throw Error('请先启用账套')
        return zq;
    },
    calAssQty: (qty,spec,unit1,unit2) => {
        qty = Math.abs(qty)
        if(!spec) throw Error('规格不能为 0 ')
        let mai = Math.floor(qty/spec) > 0 ? `${Math.floor(qty/spec)}${unit1}`:''
        let sub = qty%spec > 0 ? `${qty%spec}${unit2}` : ''
        return mai+sub
    }
}




module.exports = tool